{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# 重複しているコードをクラスにまとめる\n",
    "\n",
    "* データベースに接続する部分を、共通して使うクラスとしてまとめる\n",
    "    * データベースの変更や、パスワードの変更があったとしても1箇所の修正だけで済むようになる"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## DB接続部分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=ConnectDb\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "#classpath=\".:/Users/ftakao2007/jupyter/jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "\n",
    "class ${name} {\n",
    "  static Connection getConnection() throws Exception{\n",
    "    Connection con = null;\n",
    "    \n",
    "    //# 接続\n",
    "    con = DriverManager.getConnection(\"jdbc:postgresql://localhost:5432/ftakao2007_development\", \"ftakao2007\", \"pass\");\n",
    "    //#con = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_development\", \"jsd\", \"pass\");\n",
    "    return con;\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル(デバッグ目的。本来はコンパイルしなくても他のコードをコンパイルすると自動でこのコードもコンパイルされる)\n",
    "javac -encoding UTF-8 ${name}.java"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## select"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------------------------------------\n",
      "--- Rows:1\n",
      "---------------------------------------------------\n",
      "id:1\n",
      "name:Taro\n",
      "age:21\n",
      "address:Tokyo\n",
      "---------------------------------------------------\n",
      "--- Rows:2\n",
      "---------------------------------------------------\n",
      "id:2\n",
      "name:Hanako\n",
      "age:18\n",
      "address:Hokkaido\n",
      "---------------------------------------------------\n",
      "--- Rows:3\n",
      "---------------------------------------------------\n",
      "id:11\n",
      "name:Shiro\n",
      "age:15\n",
      "address:Ehime\n",
      "---------------------------------------------------\n",
      "--- Rows:4\n",
      "---------------------------------------------------\n",
      "id:9\n",
      "name:Ume\n",
      "age:19\n",
      "address:Wakayama\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PeopleSelect\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "#classpath=\".:/Users/ftakao2007/jupyter/jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    Connection connection = null;\n",
    "    \n",
    "    try{\n",
    "      //# 接続したオブジェクトをConnectDbから取得し、Statementオブジェクトを生成\n",
    "      //# ここでConnection connectionとすろとfinallyでcannot find symbolのエラーになる\n",
    "      //# https://stackoverflow.com/questions/15281640/cannot-find-symbol-when-trying-to-close-i-o-objects\n",
    "      //# Connection connection = ConnectDb.getConnection();\n",
    "      connection = ConnectDb.getConnection();\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行\n",
    "      resultSet = statement.executeQuery(\"SELECT * FROM people\");\n",
    "\n",
    "      //# 値の取得\n",
    "      List<String> fields = new ArrayList<String>();\n",
    "      ResultSetMetaData rsmd = resultSet.getMetaData();\n",
    "      for (int i = 1; i <= rsmd.getColumnCount(); i++) {\n",
    "        fields.add(rsmd.getColumnName(i));\n",
    "      }\n",
    "\n",
    "      //# 結果の出力\n",
    "      int rowCount = 0;\n",
    "      while (resultSet.next()) {\n",
    "        rowCount++;\n",
    "\n",
    "        System.out.println(\"---------------------------------------------------\");\n",
    "        System.out.println(\"--- Rows:\" + rowCount);\n",
    "        System.out.println(\"---------------------------------------------------\");\n",
    "\n",
    "        //# 値は、「resultSet.getString(<フィールド名>)」で取得する。\n",
    "        for (String field : fields) {\n",
    "          System.out.println(field + \":\" + resultSet.getString(field));\n",
    "        }\n",
    "      }\n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行\n",
    "java -classpath ${classpath} ${name}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PeopleInsert\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "#classpath=\".:/Users/ftakao2007/jupyter/jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    if (args.length != 3) {\n",
    "      System.out.println(\"usage: java -classpath .:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar PeopleInsert name age address\");\n",
    "      System.exit(1);\n",
    "    }\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = ConnectDb.getConnection();\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行\n",
    "      String name = args[0];\n",
    "      int age = Integer.parseInt(args[1]);\n",
    "      String address = args[2];\n",
    "      statement.executeUpdate(\"INSERT INTO people (name, age, address) VALUES ('\" + name + \"', \" + age + \", '\" + address + \"')\");\n",
    "\n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行。ここで名前、年齢、住所を引数で与える\n",
    "java -classpath ${classpath} ${name} Shiro 15 Ehime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PeopleUpdate\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "#classpath=\".:/Users/ftakao2007/jupyter/jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    if (args.length != 4) {\n",
    "      System.out.println(\"usage: java -classpath .:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar PeopleUpdate name age address id\");\n",
    "      System.exit(1);\n",
    "    }\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = ConnectDb.getConnection();\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行\n",
    "      String name = args[0];\n",
    "      int age = Integer.parseInt(args[1]);\n",
    "      String address = args[2];\n",
    "      int id = Integer.parseInt(args[3]);\n",
    "      statement.executeUpdate(\"UPDATE people SET name = '\" + name + \"', age = \" + age + \", address = '\" + address + \"' WHERE id = \" + id);\n",
    "      //# DEBUG\n",
    "      //# System.out.println(\"UPDATE people SET name = '\" + name + \"', age = \" + age + \", address = '\" + address + \"' WHERE id = \" + id);\n",
    "\n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行。ここで名前、年齢、住所、更新対象のidを引数で与える\n",
    "java -classpath ${classpath} ${name} Ume 19 Wakayama 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## delete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=PeopleDelete\n",
    "classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "#classpath=\".:/Users/ftakao2007/jupyter/jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "import java.util.ArrayList;\n",
    "import java.util.List;\n",
    "\n",
    "public class ${name} {\n",
    "  public static void main(String[] args) throws Exception{\n",
    "    Connection connection = null;\n",
    "    Statement statement = null;\n",
    "    ResultSet resultSet = null;\n",
    "    \n",
    "    if (args.length != 1) {\n",
    "      System.out.println(\"usage: java -classpath .:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar PeopleDelete id\");\n",
    "      System.exit(1);\n",
    "    }\n",
    "    \n",
    "    try{\n",
    "      //# 接続\n",
    "      connection = ConnectDb.getConnection();\n",
    "      statement = connection.createStatement();\n",
    "      \n",
    "      //# SQL文発行\n",
    "      int id = Integer.parseInt(args[0]);\n",
    "      statement.executeUpdate(\"DELETE FROM people WHERE id = \" + id);\n",
    "      //# DEBUG\n",
    "      //#System.out.println(\"DELETE FROM people WHERE id = \" + id);\n",
    "\n",
    "    } finally {\n",
    "      //# 接続を切断する\n",
    "      if (resultSet != null) {\n",
    "        resultSet.close();\n",
    "      }\n",
    "      if (statement != null) {\n",
    "        statement.close();\n",
    "      }\n",
    "      if (connection != null) {\n",
    "        connection.close();\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行。ここで削除対象のidを引数で与える\n",
    "java -classpath ${classpath} ${name} 13"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
